﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Project.Engine.Utilities;
using Project.Module.Model;
using Project.Module.DAO.BL;
using Project.Module.DAO.CMSBL;

namespace Project.Module.DAO.BL
{
    public class ProductBL : DAO.IBL.IBLTable<Product, int>
    {
        public List<ProductModel> GetListProduct(int cateId)
        {
            var query = from e in this.Context.Products
                        where e.CategoryID == cateId
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> GetListProductNew(int MaxProductNew)
        {
            int idLopHoc = DefineUtil.lopHocId;
            var query = from e in this.Context.Products
                        where e.CategoryID != idLopHoc
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                        };

            //return
            return query.Take(MaxProductNew).ToList();
        }

        public List<ProductModel> GetListProduct(int cateId, int start, int limit, ref int total)
        {
            var query = from e in this.Context.Products
                        where e.CategoryID == cateId
                        select new ProductModel() {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                        };

            total = query.Count();
            if (total <= limit) {
                //return
                return query.ToList();
            }

            //return
            return query.Skip(start).Take(limit).ToList();
        }

        public ProductModel GetDetailProduct(int productId)
        {
            var query = from e in this.Context.Products
                        where e.ProductID == productId
                        select new ProductModel()
                        {
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            if (query != null)
            {
                return query.ToList().FirstOrDefault();
            }

            return null;
        }

        public int GetCateIdByProductId(int productId) {
            var query = from e in this.Context.Products
                        where e.ProductID == productId
                        select new { CateId = e.CategoryID};
            if (query != null) {
                return ConvertUtil.ToInt32(query.Select(c => c.CateId).FirstOrDefault());
            }

            return 0;
        }

        public string GetProductNameByProductId(int productId) {
            var query = from e in this.Context.Products
                        where e.ProductID == productId
                        select new { name = e.ProductName };
            
            if(query != null){
                return ConvertUtil.ToString(query.Select(c => c.name).FirstOrDefault());
            }

            return String.Empty;
        }

        public void AddNewProduct(string name, string image, int quantity, int price, string description, int cateId, int supplierId, string displayAdmin)
        {
            int id = GetMaxId() + 1;
            (new ImageBL()).AddNewImageProduct(image, id);
            int imageId = (new ImageBL()).GetImageIdByProductId(id);
            DateTime date = DateTime.Now;

            if (imageId != 0) {
                string sql = @"insert into Product(
                                  [ProductName], [CategoryID], Price, 
                                  Description, ImageID, CreatedBy,
                                  Quantity, ProductStatus, CreatedDate, SupplierID)
                               values({0}, {1}, {2}, {3}, {4}, {5}, {6}, cast('0' as bit), {7}, {8})";
                var query = this.Context.ExecuteQuery<ProductModel>(sql, new object[] { name, cateId, price, description, imageId, displayAdmin, quantity, date, supplierId }).ToList(); 
            }
        }

        public void EditProduct(int id, string name, string image, int quantity, int price, string description, int cateId, int supplierId, string displayAdmin)
        {
            (new ImageBL()).AddNewImageProduct(image, id);
            int imageId = (new ImageBL()).GetImageIdByProductId(id);
            DateTime date = DateTime.Now;

            if (imageId != 0)
            {
                string sql = @"update Product
                               set  ProductName = {0}, CategoryID = {1}, Price = {2},
                                    Description = {3}, ImageID = {4}, ModifiedBy = {5}, 
                                    ModifiedDate = {6}, Quantity = {7}, SupplierID = {8}
                               where ProductID = {9}";
                var query = this.Context.ExecuteQuery<ProductModel>(sql, new object[] { name, cateId, price, description, imageId, displayAdmin, date, quantity, supplierId, id }).ToList();
            }
        }

        private int GetMaxId() {
            var query = from e in this.Context.Products
                        orderby e.ProductID descending
                        select new { id = e.ProductID };
            if (query != null)
            {
                return ConvertUtil.ToInt32(query.Select(c => c.id).FirstOrDefault());
            }

            return 1;
        }

        public List<ProductModel> GetListProduct() {
            var query = from e in this.Context.Products
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.Take(100).ToList();
        }

        public List<ProductModel> GetListProductNew()
        {
            var query = from e in this.Context.Products
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.Take(4).ToList();
        }

        public List<ProductModel> GetListProductHot()
        {
            var query = from e in this.Context.Products
                        orderby e.Price ascending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.Take(4).ToList();
        }

        public List<ProductModel> Search(string name, int supplierID, int price, int quantity)
        {
            var query = from e in this.Context.Products
                        where (name != String.Empty && e.ProductName.Contains(name) == true) ||
                        (supplierID != 0 && e.SupplierID == supplierID) ||
                        (price != 0 && e.Price == price) ||
                        (quantity != 0 && e.Quantity == quantity)
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> SearchProduct(string name)
        {
            var query = from e in this.Context.Products
                        where e.ProductName.Contains(name) == true
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> SearchProductBySupplierID(string name)
        {
            int supplierId = 0;
            
            var querySupplier = from e in this.Context.Suppliers
                        where e.SupplierName.Contains(name) == true
                        select new { 
                            id = e.SupplierID,
                        };

            if (querySupplier != null) {
                supplierId = ConvertUtil.ToInt32(querySupplier.Select(c => c.id).FirstOrDefault());
            }
                        
            var query = from e in this.Context.Products
                        where e.SupplierID == supplierId
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> SearchProductByPrice(int price)
        {
            var query = from e in this.Context.Products
                        where e.Price == price
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> SearchProductByGreaterPrice(int price)
        {
            var query = from e in this.Context.Products
                        where e.Price >= price
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> SearchProductByLessPrice(int price)
        {
            var query = from e in this.Context.Products
                        where e.Price <= price
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> SearchProductByQuantity(int quantity)
        {
            var query = from e in this.Context.Products
                        where e.Quantity == quantity
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> SearchProductByGreaterQuantity(int quantity)
        {
            var query = from e in this.Context.Products
                        where e.Quantity >= quantity
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public List<ProductModel> SearchProductByLessQuantity(int quantity)
        {
            var query = from e in this.Context.Products
                        where e.Quantity <= quantity
                        orderby e.CreatedDate descending
                        select new ProductModel()
                        {
                            CatagoryId = e.CategoryID,
                            ProductId = e.ProductID,
                            Description = e.Description,
                            ImageID = e.ImageID != null ? e.ImageID.Value : 0,
                            CreatedDate = e.CreatedDate.HasValue ? e.CreatedDate.Value : Convert.ToDateTime("01/01/1900"),
                            CreatedBy = e.CreatedBy,
                            ProductStatus = e.ProductStatus == true ? true : false, // true: sẵn sàng
                            ProductName = e.ProductName,
                            priceProduct = e.Price,
                            quantityProduct = e.Quantity,
                            money = e.Price * e.Quantity,
                            SupplierID = e.SupplierID != null ? e.SupplierID.Value : 0,
                        };

            //return
            return query.ToList();
        }

        public void DeleteProduct(int productId) {
            Product product = this.Context.Products.Single(c => c.ProductID == productId);
            this.Context.Products.DeleteOnSubmit(product);
            this.Context.SubmitChanges();
        }
    }
}
